package com.zjlanyun.erp.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.zjlanyun.erp.model.ICInStockBillEntryModel;

import java.util.ArrayList;
import java.util.List;

/**
 * Created by lanyun on 16/5/16.
 */
public class DBICInStockBillEntry {
    private static final String TABLE_NAME = "ICInStockBillEntry";
    private DBHelper mDBHelper;
    private SQLiteDatabase db;

    public DBICInStockBillEntry(Context context) {
        mDBHelper = new DBHelper(context);
        // 因为getWritableDatabase内部调用了mContext.openOrCreateDatabase(mName, 0, mFactory);
        // 所以要确保context已初始化,我们可以把实例化DBManager的步骤放在Activity的onCreate里
        db = mDBHelper.getWritableDatabase();
    }

    /**
     * 插入数据
     * @param ICInStockBillEntryModels
     */
    public void insert(List<ICInStockBillEntryModel> ICInStockBillEntryModels){
        db.beginTransaction(); // 开始事务
        try {
            for (ICInStockBillEntryModel model : ICInStockBillEntryModels) {
                ContentValues cv = new ContentValues();
                cv.put("FNumber", model.getFNumber());
                cv.put("FName", model.getFName());
                cv.put("FModel", model.getFModel());
                cv.put("FAuxProp1", model.getFAuxProp1());
                cv.put("FBatchNO", model.getFBatchNO());
                cv.put("FQty", model.getFQty());
                db.insert(TABLE_NAME, null, cv);
            }
            db.setTransactionSuccessful();
        } finally {
            db.endTransaction();
        }
    }

    /**
     * 更新记录
     * @param model
     */
    public void updateWithID(ICInStockBillEntryModel model){
        ContentValues cv = new ContentValues();
        cv.put("FNumber", model.getFNumber());
        cv.put("FName", model.getFName());
        cv.put("FModel", model.getFModel());
        cv.put("FAuxProp1", model.getFAuxProp1());
        cv.put("FBatchNO", model.getFBatchNO());
        cv.put("FQty", model.getFQty());
        db.update(TABLE_NAME, cv, "_id = ?", new String[] {String.valueOf(model.get_id())});
    }

    /**
     * 删除记录
     * @param model
     */
    public void deleteWithID(ICInStockBillEntryModel model) {
        db.delete(TABLE_NAME, "_id = ?", new String[] { String.valueOf(model.get_id()) });
    }

    /**
     * 查询所有，返回cursor
     * @return
     */
    public Cursor queryTheCursor() {
        Cursor c = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        return c;
    }

    /**
     * 查询所有记录并返回
     * @return ArrayList
     */
    public ArrayList<ICInStockBillEntryModel> queryAll(){
        ArrayList<ICInStockBillEntryModel> list = new ArrayList<>();
        Cursor c = db.rawQuery("SELECT * FROM " + TABLE_NAME, null);
        while (c.moveToNext()) {
            ICInStockBillEntryModel model = new ICInStockBillEntryModel();
            model.set_id(c.getInt(c.getColumnIndex("_id")));
            model.setFNumber(c.getString(c.getColumnIndex("FNumber")));
            model.setFName(c.getString(c.getColumnIndex("FName")));
            model.setFModel(c.getString(c.getColumnIndex("FModel")));
            model.setFAuxProp1(c.getString(c.getColumnIndex("FAuxProp1")));
            model.setFBatchNO(c.getString(c.getColumnIndex("FBatchNO")));
            model.setFQty(c.getString(c.getColumnIndex("FQty")));
            list.add(model);
        }
        c.close();
        return list;
    }

    /**
     * 清空表内所有记录，并重置自增字段
     */
    public void clearAll(){
        db.execSQL("DELETE FROM "+TABLE_NAME);
        db.delete("sqlite_sequence", "name = ?", new String[] {TABLE_NAME});
    }

    /**
     * 查询单条记录
     * @param _id
     * @return ICInStockBillEntryModel
     */
    public ICInStockBillEntryModel queryWithID(int _id){
        Cursor c = db.rawQuery("select * from " + TABLE_NAME + " where _id = ?", new String[] {String.valueOf(_id)});
        c.moveToNext();
        ICInStockBillEntryModel model = new ICInStockBillEntryModel();
        model.set_id(c.getInt(c.getColumnIndex("_id")));
        model.setFNumber(c.getString(c.getColumnIndex("FNumber")));
        model.setFName(c.getString(c.getColumnIndex("FName")));
        model.setFModel(c.getString(c.getColumnIndex("FModel")));
        model.setFAuxProp1(c.getString(c.getColumnIndex("FAuxProp1")));
        model.setFBatchNO(c.getString(c.getColumnIndex("FBatchNO")));
        model.setFQty(c.getString(c.getColumnIndex("FQty")));
        c.close();
        return model;
    }

    /**
     * 查询记录总数
     * @return int
     */
    public int getCount(){
        Cursor c = db.rawQuery("SELECT COUNT(*) FROM "+TABLE_NAME, null);
        c.moveToFirst();
        int count = c.getInt(0);
        c.close();
        return count;
    }

    /**
     * 关闭数据库
     */
    public void closeDB() {
        db.close();
    }

    /**
     * 更新数据list
     * @param model
     * @return
     */
    public ArrayList<ICInStockBillEntryModel> updateList(ICInStockBillEntryModel model){
        Cursor c = db.rawQuery("select * from " + TABLE_NAME + " where FNumber = ? and FAuxProp1 = ? and FBatchNO = ?", new String[] {model.getFNumber(), model.getFAuxProp1(), model.getFBatchNO()});
        if (c.getCount() > 0) {
            c.moveToNext();
        } else {

        }
        return queryAll();
    }
}
